﻿/*
 * Copyright 2009 - 2010 eScience Institute
 *
 * This file is part of SqlShare.
 *
 * SqlShare is free software: you can redistribute it and/or modify it under the terms 
 * of the GNU General Public License as published by the Free Software Foundation, 
 * either version 3 of the License, or (at your option) any later version.
 *
 * SqlShare is distributed in the hope that it will be useful, but WITHOUT ANY 
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with 
 * SqlShare. If not, see http://www.gnu.org/licenses/.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace RawData
{
    public class SqlTableMetadata
    {
        public char ColumnDelimiter { get; private set; }
       
        private int _constColumnCount;

        private int _linesRead;

        public List<SqlColumnMetadata> DataColumns { get; private set; }

        public string TableName { get; private set; }

        public SqlTableMetadata(string tablename, char delimter)
        {
            ColumnDelimiter = delimter;
            _constColumnCount = -1;
            _linesRead = 0;
            DataColumns = new List<SqlColumnMetadata>();

            // escape table name properly
            TableName = EscapeToName(tablename); ;
        }

        /// <summary>
        /// This must be ran first before reading data
        /// </summary>
        /// <param name="line"></param>
        public void ParseHeader(string line, bool hasColumnHeader)
        {
            String[] headers = line.Split(new char[] { ColumnDelimiter }, StringSplitOptions.RemoveEmptyEntries);
            _constColumnCount = headers.Length;
            for (int i = 0; i < headers.Length; i++)
            {
                DataColumns.Add(new SqlColumnMetadata((!hasColumnHeader ? "COL" + i : headers[i]), this));
            }

            // check names, make sure we do not have any duplicate column names
            Dictionary<string, bool> map = new Dictionary<string,bool>();
            foreach (SqlColumnMetadata column in DataColumns)
            {
                while (map.ContainsKey(column.ColumnName))
                {
                    // append an additional 'x' to the string if it duplicate detected
                    column.ColumnName += "x";
                }

                map.Add(column.ColumnName, true);
            }
        }

        /// <summary>
        /// After the header is ran
        /// </summary>
        /// <param name="line"></param>
        public void ParseData(string line)
        {
            _linesRead++;   // do first so we can report which line we actually reported on if throwing exception

            if (_constColumnCount == -1)
            {
                throw new Exception("Read Header needs to executed first on data");
                
            }
            String[] dataSplit = line.Split(new char[] { ColumnDelimiter }, _constColumnCount, StringSplitOptions.None);

            if (dataSplit.Length != _constColumnCount)
            {
                throw new Exception("Invalid number of column delimters. RECIEVED: " + dataSplit.Length + ", EXPECTED: " + _constColumnCount + ". On Dataline #" + _linesRead + ". DataLine:" + Environment.NewLine + line);
            }

            for (int i = 0; i < _constColumnCount; i++)
            {
                DataColumns[i].CheckData(dataSplit[i]);
            }
        
        }

        public String SQLCreateTable
        {
            get
            {
                /*
                string runCmdSyntax = "DECLARE @sqlCmd varchar(max) = {0};\nEXEC(@sqlCmd)";
                string createTableSyntax = "'CREATE TABLE ' + QUOTENAME({0}) + ' (' + " + Environment.NewLine + "{1}" + Environment.NewLine + "')'";
                string createRowSyntax = "\tQUOTENAME({0}) + ' {1},' + ";
                string createRowEndSyntax = "\tQUOTENAME({0}) + ' {1}' + ";

                string rows = "";
                for (int i = 0; i < DataColumns.Count; i++)
                {                    
                    string dataType;
                    switch (DataColumns[i].KnownDBType)
                    {
                        case SqlDbType.Int:
                            dataType = " int";
                            break;

                        case SqlDbType.Float:
                            dataType = " float";
                            break;

                        default:
                            string charSize = DataColumns[i].MaxChars > 8000 ? "max" : DataColumns[i].MaxChars.ToString();
                            dataType = " varchar(" + charSize + ")";
                            break;
                    }

                    rows += String.Format((i == DataColumns.Count - 1 ? createRowEndSyntax : createRowSyntax) + Environment.NewLine, DataColumns[i].ColumnName, dataType);
                }

                string table = string.Format(createTableSyntax, TableName, rows);
                return string.Format(runCmdSyntax, table);
                 */
                
                string createTable = "CREATE TABLE " +  TableName + " (" + Environment.NewLine;

                for (int i = 0; i < DataColumns.Count; i++)
                {
                    createTable += "\t" + DataColumns[i].ColumnName + ""; 

                    switch (DataColumns[i].KnownDBType)
                    {
                        case SqlDbType.Int:
                            createTable += " int";
                            break;

                        case SqlDbType.Float:
                            createTable += " float";
                            break;

                        default:
                            string charSize = DataColumns[i].MaxChars > 8000 ? "max" : DataColumns[i].MaxChars.ToString();
                            createTable += " varchar(" + charSize + ")";
                            break;
                    }

                    createTable += (i == DataColumns.Count-1? "" : ",") + Environment.NewLine;
                }

                createTable += ")";                 
                return createTable;

                /*
                 * string createTable = "CREATE TABLE [" +  TableName + "] (" + Environment.NewLine;

                for (int i = 0; i < DataColumns.Count; i++)
                {
                    createTable += "\t[" + DataColumns[i].ColumnName + "]"; 

                    switch (DataColumns[i].KnownDBType)
                    {
                        case SqlDbType.Int:
                            createTable += " int";
                            break;

                        case SqlDbType.Float:
                            createTable += " float";
                            break;

                        default:
                            string charSize = DataColumns[i].MaxChars > 8000 ? "max" : DataColumns[i].MaxChars.ToString();
                            createTable += " varchar(" + charSize + ")";
                            break;
                    }

                    createTable += (i == DataColumns.Count-1? "" : ",") + Environment.NewLine;
                }

                createTable += ")";                 
                return createTable;
                 **/
            }
        }

        public static string EscapeToName(string raw)
        {
            return "[" + raw + "]";
            /*
            string[] charsToEscape = new string[] { " ", ".", "-" };
            string[] wordsToEscape = new string[] { "identity", "desc", "description", "go" };
            string word = raw.ToLower();

            foreach (string badChar in charsToEscape)
            {
                word = word.Replace(badChar, "_");
            }
            foreach (string keyword in wordsToEscape)
            {
                if (word.Equals(keyword))
                {
                    word += "_";
                }
            }

            return word;
             * */
        }
    }

    public class SqlColumnMetadata
    {
        public String ColumnName { get; set; }

        /// <summary>
        /// The known DB will either be varchar, int or float
        /// we know that an INT can be a FLOAT and VARCHAR
        /// a FLOAT can be a VARCHAR
        /// </summary>
        public SqlDbType KnownDBType { get; private set; }
        public int MaxChars { get; set; }
        public bool HasEmptysOrNulls { get; set; }

        public SqlColumnMetadata(string columnName, SqlTableMetadata table)
        {
            if (String.IsNullOrEmpty(columnName))
            {
                columnName = "UNKNOWN";
            }
            ColumnName = SqlTableMetadata.EscapeToName(columnName);
            
            KnownDBType = SqlDbType.Int;
            MaxChars = 0;
            HasEmptysOrNulls = false;
        }

        public void CheckData(string dataInput)
        {
            // sometimes the string is empty or null (probably not null but easier API call)
            // in that case, lets skip the check so we do not crash the DB type
            if (String.IsNullOrEmpty(dataInput))
            {
                HasEmptysOrNulls = true;
                return;
            }


            if (KnownDBType == SqlDbType.Int)
            {
                try
                {
                    int i = int.Parse(dataInput);                    
                }
                catch (Exception e)
                {
                    KnownDBType = SqlDbType.Float;
                }
            }

            if (KnownDBType == SqlDbType.Float)
            {
                try
                {                    
                    double d = double.Parse(dataInput);                    
                }
                catch (Exception e)
                {
                    KnownDBType = SqlDbType.VarChar;
                }
            }

            // otherwise varchar, keep track of maxsize (for varchar, we need to have 1 extra space)
            MaxChars = Math.Max(dataInput.Length, MaxChars);
        }
    }
}
